from flask import Flask, render_template, request, redirect, url_for, g, flash
import mysql.connector
from mysql.connector import errorcode
from datetime import datetime

app = Flask(__name__)
# 确保设置一个安全的密钥，用于 flash 消息等会话管理
app.secret_key = 'shuweinicai_bu_dao_de_mi_yao_123!@#' 

# --- 数据库配置 ---
DB_CONFIG = {
    'user': 'bookkeeper',
    'password': '12345678', # 你的 MySQL 'bookkeeper' 用户密码
    'host': 'localhost',
    'database': 'bookkeeping_db', # 你创建的数据库名
    'raise_on_warnings': True, # 在开发时开启警告
}

def get_db_connection():
    """获取数据库连接。如果 g 对象中没有连接，则创建一个新的。"""
    try:
        # 检查 g.db 是否存在，或者存在但已关闭
        if 'db' not in g or not hasattr(g.db, 'is_connected') or not g.db.is_connected():
            g.db = mysql.connector.connect(**DB_CONFIG)
        return g.db
    except mysql.connector.Error as err:
        app.logger.error(f"数据库连接错误: {err}") 
        flash("数据库连接失败，请稍后重试或联系管理员。", "error")
        return None
    except Exception as e:
        app.logger.error(f"获取数据库连接时发生未知错误: {e}")
        flash("发生未知错误，请联系管理员。", "error")
        return None

@app.teardown_appcontext
def close_db_connection(exception):
    """在应用上下文结束时关闭数据库连接。"""
    db = g.pop('db', None)
    if db is not None and hasattr(db, 'is_connected') and db.is_connected():
        try:
            db.close()
        except Exception as e:
            app.logger.error(f"关闭数据库连接时出错: {e}")


def init_table():
    """初始化数据库表 (如果不存在)。"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(user=DB_CONFIG['user'],
                                       password=DB_CONFIG['password'],
                                       host=DB_CONFIG['host'])
        cursor = conn.cursor()
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_CONFIG['database']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
        cursor.execute(f"USE {DB_CONFIG['database']}")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS expenses (
                id INT AUTO_INCREMENT PRIMARY KEY,
                date DATE NOT NULL,
                amount DECIMAL(10, 2) NOT NULL,
                main_category VARCHAR(255) NOT NULL,
                sub_category VARCHAR(255),
                payment_method VARCHAR(255),
                notes TEXT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """)
        conn.commit()
        print("expenses 表已检查/创建。")
    except mysql.connector.Error as err:
        print(f"创建表时出错: {err}")
    finally:
        if cursor:
            try:
                cursor.close()
            except: pass # 忽略关闭游标时的错误
        if conn and conn.is_connected():
            try:
                conn.close()
            except: pass # 忽略关闭连接时的错误

@app.cli.command('init-db-mysql')
def init_db_cli():
    """Flask CLI 命令，用于初始化数据库表。"""
    init_table()

@app.route('/', methods=['GET', 'POST'])
def index():
    """主页路由，处理账目显示、添加和查询/排序。"""
    conn = get_db_connection()
    if not conn:
        return render_template('index.html', expenses=[], query_params={})

    cursor = None # 初始化为 None
    expenses_list = [] # 初始化为空列表

    try:
        cursor = conn.cursor(dictionary=True)

        if request.method == 'POST':
            date_str = request.form.get('date')
            amount_str = request.form.get('amount')
            main_category = request.form.get('main_category')
            sub_category = request.form.get('sub_category', '')
            payment_method = request.form.get('payment_method', '')
            notes = request.form.get('notes', '')

            if not date_str or not amount_str or not main_category:
                flash("日期、金额和主要分类不能为空！", "error")
            else:
                temp_cursor_post = None # 用于POST操作的临时游标
                try:
                    date_obj = datetime.strptime(date_str, '%Y/%m/%d')
                    formatted_date = date_obj.strftime('%Y-%m-%d')
                    amount = float(amount_str)
                    query = """
                        INSERT INTO expenses (date, amount, main_category, sub_category, payment_method, notes)
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """
                    temp_cursor_post = conn.cursor() 
                    temp_cursor_post.execute(query, (formatted_date, amount, main_category, sub_category, payment_method, notes))
                    conn.commit()
                    flash("账目添加成功！", "success")
                except ValueError:
                    flash("日期或金额格式无效！", "error")
                except mysql.connector.Error as err:
                    flash(f"数据库错误 (添加时): {err}", "error")
                except Exception as e:
                    flash(f"发生未知错误 (添加时): {e}", "error")
                finally:
                    if temp_cursor_post: 
                        try: temp_cursor_post.close()
                        except: pass
            # POST请求后总是重定向，以避免刷新时重复提交
            return redirect(url_for('index'))

        # --- GET 请求的查询和排序逻辑 ---
        q_date_start = request.args.get('date_start', '')
        q_date_end = request.args.get('date_end', '')
        q_amount_min = request.args.get('amount_min', '')
        q_amount_max = request.args.get('amount_max', '')
        q_main_category = request.args.get('main_category', '')
        
        sort_by_col = request.args.get('sort_by', 'date')
        sort_order_val = request.args.get('sort_order', 'DESC').upper()
        if sort_order_val not in ['ASC', 'DESC']: sort_order_val = 'DESC'
        allowed_sort_columns = ['date', 'amount', 'main_category']
        if sort_by_col not in allowed_sort_columns: sort_by_col = 'date'

        sql_query = "SELECT * FROM expenses WHERE 1=1"
        params = []

        try:
            if q_date_start:
                sql_query += " AND date >= %s"
                params.append(datetime.strptime(q_date_start, '%Y/%m/%d').strftime('%Y-%m-%d'))
            if q_date_end:
                sql_query += " AND date <= %s"
                params.append(datetime.strptime(q_date_end, '%Y/%m/%d').strftime('%Y-%m-%d'))
            if q_amount_min:
                sql_query += " AND amount >= %s"
                params.append(float(q_amount_min))
            if q_amount_max:
                sql_query += " AND amount <= %s"
                params.append(float(q_amount_max))
            if q_main_category:
                sql_query += " AND main_category LIKE %s"
                params.append(f"%{q_main_category}%")
        except ValueError:
            flash("查询参数中的日期或金额格式无效。", "warning")
            # 如果参数格式错误，不应用筛选条件，但仍执行基础查询和排序
        except Exception as e:
            flash(f"处理查询参数时出错: {e}", "error")
            # 同样不应用筛选条件

        sql_query += f" ORDER BY {sort_by_col} {sort_order_val}, id {sort_order_val}"
        
        cursor.execute(sql_query, tuple(params))
        expenses_list = cursor.fetchall()
        
    except mysql.connector.Error as err:
        flash(f"数据库错误 (查询时): {err}", "error")
    except Exception as e:
        flash(f"发生未知错误 (查询时): {e}", "error")
    finally:
        if cursor:
            try:
                cursor.close()
            except Exception as e_close:
                app.logger.error(f"关闭游标时出错 (index): {e_close}")
            
    return render_template('index.html', expenses=expenses_list, query_params=request.args)

@app.route('/delete/<int:id>')
def delete_expense(id):
    """删除指定ID的账目记录。"""
    conn = get_db_connection()
    if not conn:
        return redirect(url_for('index'))
    
    cursor = None
    try:
        cursor = conn.cursor()
        cursor.execute('DELETE FROM expenses WHERE id = %s', (id,))
        conn.commit()
        flash("记录删除成功！", "success")
    except mysql.connector.Error as err:
        flash(f"删除记录时出错: {err}", "error")
    except Exception as e:
        flash(f"发生未知错误 (删除时): {e}", "error")
    finally:
        if cursor: 
            try: cursor.close()
            except: pass
    return redirect(url_for('index'))

@app.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit_expense(id):
    """编辑指定ID的账目记录。"""
    conn = get_db_connection()
    if not conn:
        return redirect(url_for('index'))
    
    cursor = None 
    expense_for_form = None # 用于在出错时回填表单

    try:
        cursor = conn.cursor(dictionary=True)
        
        if request.method == 'POST':
            date_str = request.form['date']
            amount_str = request.form['amount']
            main_category = request.form['main_category']
            sub_category = request.form.get('sub_category', '')
            payment_method = request.form.get('payment_method', '')
            notes = request.form.get('notes', '')
            
            try:
                date_obj = datetime.strptime(date_str, '%Y/%m/%d')
                formatted_date = date_obj.strftime('%Y-%m-%d')
                amount = float(amount_str)
                
                query = """
                    UPDATE expenses SET date=%s, amount=%s, main_category=%s, 
                    sub_category=%s, payment_method=%s, notes=%s WHERE id=%s
                """
                # 使用独立的游标执行更新操作
                update_cursor = conn.cursor()
                update_cursor.execute(query, (formatted_date, amount, main_category, sub_category, payment_method, notes, id))
                conn.commit()
                if update_cursor: update_cursor.close()
                flash("记录更新成功！", "success")
                return redirect(url_for('index'))

            except ValueError:
                flash("日期或金额格式无效！请重新输入。", "error")
                # 准备回填表单的数据
                expense_for_form = {
                    'id': id, 'date': date_str, 'amount': amount_str, 
                    'main_category': main_category, 'sub_category': sub_category,
                    'payment_method': payment_method, 'notes': notes
                }
            except mysql.connector.Error as err:
                flash(f"更新记录时数据库出错: {err}", "error")
                # 尝试获取原始数据回填
                cursor.execute('SELECT * FROM expenses WHERE id = %s', (id,))
                expense_for_form = cursor.fetchone()
                if expense_for_form:
                    try:
                        date_obj_db = datetime.strptime(str(expense_for_form['date']), '%Y-%m-%d')
                        expense_for_form['date'] = date_obj_db.strftime('%Y/%m/%d')
                    except: pass # 保留原始数据库日期字符串
            except Exception as e:
                flash(f"更新记录时发生未知错误: {e}", "error")
            
            # 如果POST请求中出现错误，重新渲染编辑页面并填充用户输入或原始数据
            return render_template('edit_expense.html', expense=expense_for_form if expense_for_form else {'id': id})


        # --- GET 请求: 显示编辑表单 ---
        cursor.execute('SELECT * FROM expenses WHERE id = %s', (id,))
        expense = cursor.fetchone()

        if expense is None:
            flash("记录未找到!", "error")
            return redirect(url_for('index'))

        editable_expense = dict(expense)
        try:
            date_obj = datetime.strptime(str(expense['date']), '%Y-%m-%d')
            editable_expense['date'] = date_obj.strftime('%Y/%m/%d')
        except (ValueError, TypeError):
            editable_expense['date'] = str(expense['date']) 

        return render_template('edit_expense.html', expense=editable_expense)

    except mysql.connector.Error as err:
        flash(f"处理编辑请求时数据库出错: {err}", "error")
        return redirect(url_for('index'))
    except Exception as e:
        flash(f"处理编辑请求时发生未知错误: {e}", "error")
        return redirect(url_for('index'))
    finally:
        if cursor:
            try: cursor.close()
            except: pass

@app.route('/statistics', methods=['GET', 'POST'])
def statistics():
    """显示消费统计页面。"""
    conn = get_db_connection()
    if not conn:
        return render_template('stats.html', stats=[], selected_year='', selected_month='')

    cursor = None
    stats_data = []
    current_dt = datetime.now()
    year = request.form.get('year', str(current_dt.year))
    month = request.form.get('month', '')
    
    try:
        cursor = conn.cursor(dictionary=True)
        query = "SELECT main_category, SUM(amount) as total FROM expenses WHERE YEAR(date) = %s"
        params = [year]
        
        if month:
            try:
                month_int = int(month)
                if 1 <= month_int <= 12:
                    query += " AND MONTH(date) = %s"
                    params.append(month_int)
                else:
                    flash("月份输入无效 (应为1-12)，将统计全年数据。", "warning")
                    month = ''
            except ValueError:
                flash("月份输入不是有效数字，将统计全年数据。", "warning")
                month = ''
                
        query += " GROUP BY main_category ORDER BY total DESC"
        
        cursor.execute(query, tuple(params))
        stats_data = cursor.fetchall()
            
    except mysql.connector.Error as err:
        flash(f"查询统计数据时出错: {err}", "error")
    except Exception as e:
        flash(f"查询统计数据时发生未知错误: {e}", "error")
    finally:
        if cursor:
            try: cursor.close()
            except: pass
            
    return render_template('stats.html', stats=stats_data, selected_year=year, selected_month=month)

@app.context_processor
def inject_now():
    return {'now': datetime.utcnow()}

if __name__ == '__main__':
    # 使用 `flask run` 命令来启动开发服务器
    # app.run(debug=True, host='0.0.0.0', port=5000) # debug=True 只用于开发
    pass
